package com.skhu.as.dao;

import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;

import com.skhu.as.domain.DutyHours;
import com.skhu.as.domain.FaultHandling;
import com.skhu.as.domain.FaultHandlingDomain;
import com.skhu.as.domain.ProcessStatus;
import com.skhu.as.domain.RequestType;
import com.skhu.as.domain.Requester;
import com.skhu.as.domain.Worker;
import com.skhu.as.mapper.DutyHoursM;
import com.skhu.as.mapper.FaultHandlingM;
import com.skhu.as.mapper.ProcessStatusM;
import com.skhu.as.mapper.RequestTypeM;
import com.skhu.as.mapper.RequesterM;
import com.skhu.as.mapper.WorkerM;

public class FaultHandlingD {

	private JdbcTemplate jdbcTemplate;

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	
	public List<Worker> getWorkerList(){
		String sql = "SELECT worker_no, name, major, contact_number, use FROM worker WHERE use = 'use'";
		List<Worker> workerList = jdbcTemplate.query(sql, new WorkerM());
		return workerList;
	}
	
	public List<DutyHours> getDutyHoursList(){
		String sql = "SELECT duty_hours_no, duty_hours FROM duty_hours";
		List<DutyHours> dutyHoursList = jdbcTemplate.query(sql, new DutyHoursM());
		return dutyHoursList;
	}

	public List<RequestType> getRequestTypeList(){
		String sql = "SELECT request_type_no, request_type FROM request_type";
		List<RequestType> requestTypeList = jdbcTemplate.query(sql, new RequestTypeM());
		return requestTypeList;
	}
	
	public List<ProcessStatus> getProcessStatusList(){
		String sql = "SELECT process_status_no, process_status FROM process_status";
		List<ProcessStatus> processStatusList = jdbcTemplate.query(sql, new ProcessStatusM());
		return processStatusList;
	}
	
	public int getRequesterMaxNo(){
		String sql = "SELECT MAX(requester_no) FROM requester";
		int result = jdbcTemplate.queryForInt(sql);
		return result;
	}
	
	public int getFaultHandlingMaxNo(){
		String sql = "SELECT MAX(fault_handling_no) FROM fault_handling";
		int result = jdbcTemplate.queryForInt(sql);
		return result;
	}
	
	public int insertRequester(Requester requester){
		String sql = "INSERT INTO requester VALUES (?,?,?,?,?)";
		Object[] param = new Object[]{
				requester.getRequesterNo(),
				requester.getName(),
				requester.getContactNumber(),
				requester.getRequestPlace(),
				requester.getDepartment()
		};
		int result = jdbcTemplate.update(sql, param);
		return result;
	}
	
	public int insertFaultHandling(FaultHandling faultHandling){
		String sql = "INSERT INTO fault_handling VALUES (?,?,?,?,?,?,?,?,?,?,?)";
		Object[] param = new Object[]{
				faultHandling.getFaultHandlingNo(),
				faultHandling.getReceptionistNo(),
				faultHandling.getDutyHoursNo(),
				faultHandling.getRequestTypeNo(),
				faultHandling.getRequesterNo(),
				faultHandling.getFirstWorkerNo(),
				faultHandling.getProcessStatusNo(),
				faultHandling.getReceiptDate(),
				faultHandling.getProcessDate(),
				faultHandling.getRequestContent(),
				faultHandling.getProcessingInformation()
		};
		int result = jdbcTemplate.update(sql, param);
		return result;
	}
	
	public FaultHandling getFaultHandlingByNo(int faultHandlingNo){
		String sql = "SELECT " +
						"fault_handling_no, " +
						"receptionist_no, " +
						"duty_hours_no, " +
						"request_type_no, " +
						"requester_no, " +
						"first_worker_no, " +
						"process_status_no, " +
						"receipt_date, " +
						"process_date, " +
						"request_content, " +
						"processing_information " +
					"FROM " +
						"fault_handling " +
					"WHERE " +
						"fault_handling_no = ?";
		Object[] param = new Object[]{
				faultHandlingNo
		};
		FaultHandling faultHandling = (FaultHandling)jdbcTemplate.queryForObject(sql, param, new FaultHandlingM());
		return faultHandling;
	}
	
	public Requester getRequesterByNo(int requesterNo){
		String sql = "SELECT requester_no, name, contact_number, request_place, department FROM requester WHERE requester_no = ?";
		Object[] param = new Object[]{
				requesterNo
		};
		Requester requester = (Requester)jdbcTemplate.queryForObject(sql, param, new RequesterM());
		return requester;
	}
	
	public int updateRequester(Requester requester){
		String sql = "UPDATE requester " +
						"SET " +
							"name = ?, " +
							"contact_number = ?, " +
							"request_place = ?, " +
							"department = ? " +
						"WHERE " +
							"requester_no = ?";
		Object[] param = new Object[]{
				requester.getName(),
				requester.getContactNumber(),
				requester.getRequestPlace(),
				requester.getDepartment(),
				requester.getRequesterNo()
		}; 
		
		int result = jdbcTemplate.update(sql, param);
		
		return result;
	}
	
	public int updateFaultHandling(FaultHandling faultHandling){
		
		String sql = "UPDATE " +
						"fault_handling " +
					"SET " +
						"receptionist_no = ?, " + 
						"duty_hours_no = ?, " +
						"request_type_no = ?, " +
						"requester_no = ?, " +
						"first_worker_no = ?, " +
						"process_status_no = ?, " +
						"receipt_date = ?, " +
						"process_date = ?, " +
						"request_content = ?, " +
						"processing_information = ? " + 
					"WHERE " +
						"fault_handling_no = ?";
		
		Object[] param = new Object[]{
				faultHandling.getReceptionistNo(),
				faultHandling.getDutyHoursNo(),
				faultHandling.getRequestTypeNo(),
				faultHandling.getRequesterNo(),
				faultHandling.getFirstWorkerNo(),
				faultHandling.getProcessStatusNo(),
				faultHandling.getReceiptDate(),
				faultHandling.getProcessDate(),
				faultHandling.getRequestContent(),
				faultHandling.getProcessingInformation(),
				faultHandling.getFaultHandlingNo()
		};
		
		int result = jdbcTemplate.update(sql, param);
		
		return result;
	}
	
	public int deleteRequester(int requesterNo){
		String sql = "DELETE FROM requester WHERE requester_no = ?";
		Object[] param = new Object[]{
				requesterNo
		};
		int result = jdbcTemplate.update(sql, param);
		return result;
	}
	
	public int deleteFaultHandling(int faultHandlingNo){
		String sql = "DELETE FROM fault_handling WHERE fault_handling_no = ?";
		Object[] param = new Object[]{
				faultHandlingNo
		};
		int result = jdbcTemplate.update(sql, param);
		return result;
	}
	
}
